package database.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;

import com.mysql.jdbc.exceptions.MySQLIntegrityConstraintViolationException;

import database.objects.DatabaseObject;
import database.objects.Tag;
import functions.StaticFunctions;

import twitter4j.Status;
import twitter4j.internal.org.json.JSONArray;
import twitter4j.internal.org.json.JSONException;
import twitter4j.internal.org.json.JSONObject;

/**
 * Model for Tags.
 * 
 * @author Project group 9
 * 
 */
public class TagModel extends Model {

	private MessageModel messageModel;

	/**
	 * Constructor for TagModel
	 * 
	 * @param connection
	 *            : The connection with the database.
	 */
	public TagModel(Connection con, MessageModel messageModel) {
		super(con);
		this.messageModel = messageModel;
	}

	/**
	 * Gets a tag by a id.
	 * 
	 * @param id
	 *            : The id of the tag.
	 * @return Tag object, fake if it can't be found.
	 */
	public Tag byId(long id) {
		return (Tag) load("id", id, null).get(0);
	}

	/**
	 * Method that returns a Tag object based upon a name.
	 * 
	 * @param name
	 *            : The tag you are searching for.
	 * @return Tag object, fake one if it can't be found.
	 */
	public Tag byName(String name) {
		return (Tag) load("name", -1, name).get(0);
	}

	/**
	 * Method that executes a query to get the top tags from the database.
	 * Usefull for a tagcloud.
	 * 
	 * @return HashMap<String, Long>, String: Contains all the tags from top to
	 *         bottom Long: Contains a total.
	 */
	public ArrayList<ArrayList> getTopTags() {

		ArrayList<ArrayList> tags = new ArrayList<ArrayList>();

		try {
			PreparedStatement pst = con.prepareStatement(""
					+ "SELECT name, count( * ) as total "
					+ "FROM tag t INNER JOIN message_tag mt on mt.tag = t.id "
					+ "INNER JOIN message m on m.id = mt.message "
					+ "AND m.created_at > ? " + "AND t.visible = 1 "
					+ "GROUP BY t.id " + "ORDER BY total DESC " + "LIMIT 9");

			Calendar today = Calendar.getInstance();
			today.add(Calendar.DATE, -7);
			java.sql.Date yesterday = new java.sql.Date(today.getTimeInMillis());

			pst.setString(1, yesterday.toString());
			ResultSet rs = pst.executeQuery();
			if (rs.first()) {
				do {
					ArrayList tag = new ArrayList();
					tag.add(rs.getString("name"));
					tag.add(rs.getLong("total"));
					tags.add(tag);
				} while (rs.next());
			}
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tags;
	}

	public ArrayList<ArrayList> getTopTags(JSONArray keywords) {

		ArrayList<ArrayList> tags = new ArrayList<ArrayList>();

		String qryString = "";
		String notEquals = "";
		boolean first = true;

		try {

			for (int i = 0; i < keywords.length(); i++) {
				if (first) {
					qryString += "t.name = '" + keywords.getString(i) + "'";
					notEquals += "t.name <> '" + keywords.getString(i) + "'";
					first = false;
				} else {
					qryString += " OR t.name = '" + keywords.getString(i) + "'";
					notEquals += " AND t.name <> '" + keywords.getString(i)
							+ "'";
				}
			}

			String qry = "SELECT m.id as id " + "FROM message m "
					+ "INNER JOIN message_tag mt ON m.id = mt.message "
					+ "INNER JOIN tag t ON mt.tag = t.id " + "AND ( "
					+ qryString + ") " + "AND m.created_at > ? "
					+ "GROUP BY mt.message " + "HAVING count(*) >= "
					+ keywords.length();

			PreparedStatement pst = con.prepareStatement(qry);

			Calendar today = Calendar.getInstance();
			today.add(Calendar.DATE, -7);
			java.sql.Date yesterday = new java.sql.Date(today.getTimeInMillis());
			System.out.println(yesterday.toString());

			pst.setString(1, yesterday.toString());

			ResultSet rs = pst.executeQuery();
			String messages = " ";

			if (rs.first()) {
				do {
					messages += rs.getString("id");
					if (!rs.isLast()) {
						messages += ", ";
					}
				} while (rs.next());
			}

			System.out.println(messages);

			if (!messages.equals(" ")) {

				pst = con.prepareStatement(qry);

				qry = "SELECT t.name as name, COUNT( * ) AS total "
						+ "FROM tag t "
						+ "INNER JOIN message_tag mt ON t.id = mt.tag "
						+ "AND mt.message " + "IN ( " + messages + " ) "
						+ "AND " + notEquals + " " + "AND t.visible = 1 "
						+ "GROUP BY t.id " + "ORDER BY total DESC " + "LIMIT 9";

				System.out.println(qry);
				pst = con.prepareStatement(qry);

				rs = pst.executeQuery();
				if (rs.first()) {
					do {
						ArrayList tag = new ArrayList();
						tag.add(rs.getString("name"));
						tag.add(rs.getLong("total"));
						tags.add(tag);
					} while (rs.next());
				}
			} else {
				ArrayList tag = new ArrayList();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (JSONException e) {
			e.printStackTrace();
		}
		return tags;
	}

	/**
	 * Function to get a timeline of the tags. This is used in the frontend to
	 * generate a chart with the timeline.
	 * 
	 * @return ArrayList<HashMap<String, ArrayList<Integer>>>: ArrayList with
	 *         the labels and values for the timeline. HashMap<String,
	 *         ArrayList<Integer>>: HashMap with the labels and chart data.
	 *         String: The label used in the chart. ArrayList<Integer>: The data
	 *         to draq the cart with.
	 * @throws JSONException
	 *             : When an JSON object can't be converted.
	 */
	public ArrayList<HashMap<String, int[]>> getTimelineTagData(
			JSONObject parameters) throws JSONException {
		ArrayList<HashMap<String, int[]>> timelineData = new ArrayList<HashMap<String, int[]>>();
		HashMap<String, int[]> labelHashMap = new HashMap<String, int[]>();

		Integer history = parameters.getInt("history");
		String timeUnit = parameters.getString("timeUnit");
		JSONArray labels = parameters.getJSONArray("labels");
		Integer time = 0;
		int minTags = 4;
		HashMap<String, String> granularity = new HashMap<String, String>();
		granularity.put("MINUTE", "%Y-%m-%d %H:%i");
		granularity.put("HOUR", "%Y-%m-%d %H");
		granularity.put("DAY", "%Y-%m-%d");
		granularity.put("WEEK", "%Y-%u");
		granularity.put("MONTH", "%Y-%m");
		granularity.put("YEAR", "%Y");
		ArrayList<String> dateArray = StaticFunctions.getDateArray(timeUnit,
				history);
		if (labels.length() < minTags) {
			ArrayList<ArrayList> topTags;
			if (labels.isNull(0))
				topTags = getTopTags();
			else
				topTags = getTopTags(labels);
			int extraTags = minTags - labels.length();
			for (int i = 0; i < extraTags && i < topTags.size(); i++) {
				ArrayList array = topTags.get(i);
				labels.put(array.get(0));
			}
		}
		for (int i = 0; i < labels.length(); ++i) {
			String label = labels.getString(i);
			int[] chartData = new int[history];
			try {
				PreparedStatement pst = con
						.prepareStatement("SELECT DATE_FORMAT(m.created_at, '"
								+ granularity.get(timeUnit)
								+ "') as date, count(*) as count "
								+ "FROM tag t "
								+ "INNER JOIN message_tag mt ON t.id = mt.tag "
								+ "INNER JOIN message m ON mt.message = m.id "
								+ "AND t.name = ? "
								+ "AND m.created_at > DATE_SUB(CURDATE(), INTERVAL "
								+ (history - 1) + " " + timeUnit + ") "
								+ "GROUP BY DATE_FORMAT(m.created_at, '"
								+ granularity.get(timeUnit) + "')");

				pst.setString(1, label);
				ResultSet rs = pst.executeQuery();
				if (rs.first()) {
					do {
						for (int j = 0; j < dateArray.size(); j++) {
							if (dateArray.get(j).equals(rs.getString("date"))) {
								chartData[j] = Integer.parseInt(rs
										.getString("count"));
								break;
							}
						}
					} while (rs.next());
				} else {
					labelHashMap.put(label, chartData);
				}
				labelHashMap.put(label, chartData);
				pst.close();
			} catch (SQLException e) {
				System.out.println(e);
			}
			timelineData.add(labelHashMap);
		}
		return timelineData;
	}

	/**
	 * Loads a tag from the database, based upon a column, and the id or value
	 * that that column should be. Either the id or the value is searched for.
	 * To search on a string, set id on -1.
	 * 
	 * @param column
	 *            The column that shouls be searched in.
	 * @param id
	 *            The id that is searched for.
	 * @param value
	 *            The value that is searched for.
	 * @return ArrayList<DatabaseObject> An arraylist with databaseobjects in
	 *         it. The database object is in this case a tag.
	 */
	public ArrayList<DatabaseObject> load(String column, long id, String value) {
		ArrayList<DatabaseObject> tags = new ArrayList<DatabaseObject>();
		try {
			PreparedStatement pst = con

			.prepareStatement("SELECT * FROM `tag` where " + column + " = ?");

			if (id != -1) {
				pst.setLong(1, id);
			} else {
				pst.setString(1, value);
			}
			ResultSet rs = pst.executeQuery();
			if (rs.first()) {
				do {
					tags.add(new Tag(rs.getLong("id"), rs.getString("name"), rs
							.getBoolean("visible")));
				} while (rs.next());
			} else {
				tags.add(new Tag());
			}
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return tags;
	}

	/**
	 * Saves a tag into the database
	 * 
	 * @param status
	 *            The status of a message.
	 * @param tag
	 *            The name of the tag you want to save.
	 */
	public void save(Status status, String tag, boolean visible) {
		try {
			PreparedStatement pst = con
					.prepareStatement("INSERT INTO `tag` (`name`, `visible`) VALUES(?, ?)");
			pst.setString(1, tag);
			pst.setBoolean(2, visible);
			pst.executeUpdate();
			pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Link a tag to a message by inserting a record in the message_tags table.
	 * 
	 * @param status
	 *            The status of a message
	 * @param tag
	 *            The tag you want to insert
	 */
	public void linkMessageTag(Status status, String tag) {
		try {
			PreparedStatement pst = con
					.prepareStatement("INSERT INTO message_tag (message, tag) VALUES (?,?)");
			pst.setLong(1, messageModel.byTweetId(status.getId()).getId());
			pst.setLong(2, byName(tag).getId());
			pst.executeUpdate();
			pst.close();
		} catch (MySQLIntegrityConstraintViolationException e) {
			System.out
					.println("Error: Duplicate entry, but it doens't change the database so don't worry.");
		} catch (SQLException e) {
			e.printStackTrace();

		}
	}

	/**
	 * @deprecated Use save(String name)
	 */
	public void save(Status status) {
	}
}
